from config import logger
from llm_client import LLMClient
from typing import Dict, Any, Optional
import json


class SQLBuilder:
    @staticmethod
    def analyze_sql_error(error_message: str, sql: str) -> str:
        try:
            prompt = f"""
            请分析以下MYSQL错误信息，给出解决的建议

            错误信息:{error_message}
            SQL语句:{sql}

            请给出
            1.错误原因分析
            2.具体修复建议
            3.正确的SQL示例(如有必要)

            请用中文回答
            """
            suggestion = LLMClient.call_llm(prompt)
            if suggestion:
                return suggestion.strip()
            else:
                return "无法分析错误原因，请检查SQL语法和数据库连接是否正常"
        except Exception as e:
            logger.warning(f"错误分析失败:{str(e)}")
            return "无法分析错误原因，请检查SQL语法和数据库连接是否正常"

    @staticmethod
    def build_sql_query(parsed: Dict[str, Any]) -> Optional[str]:
        try:
            prompt = f"""
            请根据以下的解析结果生成MYSQL 的SQL语句
            解析结果为:{json.dumps(parsed,ensure_ascii=False)}
            数据库表结构：
            CREATE TABLE `scores`  (
            `score_id` int NOT NULL AUTO_INCREMENT COMMENT '成绩ID',
            `student_id` int NOT NULL COMMENT '学生ID',
            `subject_id` int NOT NULL COMMENT '科目ID',
            `exam_date` date NOT NULL COMMENT '考试日期',
            `exam_type` enum('期中','期末','单元测试','月考') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '期末' COMMENT '考试类型',
            `score` decimal(5, 2) NOT NULL COMMENT '分数',
            `grade_level` enum('优秀','良好','及格','不及格') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '等级',
            `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            PRIMARY KEY (`score_id`) USING BTREE,
            UNIQUE INDEX `uk_student_subject_exam`(`student_id` ASC, `subject_id` ASC, `exam_date` ASC, `exam_type` ASC) USING BTREE,
            INDEX `subject_id`(`subject_id` ASC) USING BTREE,
            CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
            CONSTRAINT `scores_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE ON UPDATE RESTRICT
            ) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '成绩信息表' ROW_FORMAT = DYNAMIC;


            CREATE TABLE `students`  (
            `student_id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID',
            `student_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学号',
            `student_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学生姓名',
            `gender` enum('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '男' COMMENT '性别',
            `class_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '班级名称',
            `birth_date` date NULL DEFAULT NULL COMMENT '出生日期',
            `parent_phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '家长电话',
            `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            PRIMARY KEY (`student_id`) USING BTREE,
            UNIQUE INDEX `uk_student_no`(`student_no` ASC) USING BTREE
            ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生信息表' ROW_FORMAT = DYNAMIC;


            CREATE TABLE `subjects`  (
            `subject_id` int NOT NULL AUTO_INCREMENT COMMENT '科目ID',
            `subject_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '科目名称',
            `teacher_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '任课老师',
            `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            PRIMARY KEY (`subject_id`) USING BTREE,
            UNIQUE INDEX `uk_subject_name`(`subject_name` ASC) USING BTREE
            ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '科目信息表' ROW_FORMAT = DYNAMIC;
            
            要求:
            1.如果是SELECT查询，选择合适的字段，避免SELECT *
            2.多表查询的时候使用合适的JOIN
            3.添加合适的WHERE条件
            4.有order_by的时候添加ORDER By
            5.有limit的时候添加 LIMIT
            6.只返回完整的SQL语句，不要有任何解释
           
            示例: SELECT s.student_name, s.class_name, sc.score FROM students s JOIN scores sc ON s.student_id = sc.student_id WHERE sc.score >= 80 ORDER BY sc.score DESC LIMIT 10
            """
            sql = LLMClient.call_llm_sql(prompt)
            if sql is None:
                logger.warning(f"LLM生成SQL失败，使用默认的SQL")
                return "SELECT * FROM students"
            else:
                return sql
        except Exception as e:
            logger.warning(f"LLM生成SQL失败，使用默认的SQL:{str(e)}")
            return "SELECT * FROM students"
